/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Bussiness;

import Connection.DBUtil;
import Connection.Project;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

/**
 *
 * @author SONY
 */
public class ProjectDAO {
    private static Connection conn = null;
    private static CallableStatement cs = null;
    private static ResultSet rs = null;
    private static Vector project;
    private static DBUtil objDBUtil = new DBUtil();
    private static Project objProject = new Project();

    public static void insertLocationToDB(String projectID, String projectName, int projectAllowance) throws SQLException{
        String SQL;
        DBUtil objDBUtil = new DBUtil();
        Connection conn = objDBUtil.connect();
        SQL = "INSERT INTO Project VALUES (?, ?, ?)";
            PreparedStatement pstmt = conn.prepareStatement(SQL);
            pstmt.setString(1, projectID);
            pstmt.setString(2, projectName);
            pstmt.setInt(3, projectAllowance);
            pstmt.executeUpdate();
            conn.close();
    }
    
    public static void upDatetoDB(String projectID, String projectName, String projectAllowance) throws SQLException{
        String SQL;
        DBUtil objDBUtil = new DBUtil();
        Connection conn = objDBUtil.connect();    
        SQL = "UPDATE Project SET projectID=?, projectName=?, projectAllowance=?" + " WHERE ProjectID=?";
            PreparedStatement pstmt = conn.prepareStatement(SQL);
            pstmt.setString(4, projectID);
            pstmt.setString(1, projectID);
            pstmt.setString(2, projectName);
            pstmt.setString(3, projectAllowance);
                //buoc 5: thuc thi cau lenh sql
            pstmt.executeUpdate();
            conn.close();
    }
    
    public static void deleteToDB(String projectID) throws SQLException{
        String SQL;
        DBUtil objDBUtil = new DBUtil();
        Connection conn = objDBUtil.connect(); 
        SQL = "DELETE FROM Project WHERE ProjectID=?";
        PreparedStatement pstmt = conn.prepareStatement(SQL);
        //String DepartmentID = tbBang.getValueAt(tbBang.getSelectedRow(), 1).toString();
        pstmt.setString(1, projectID);
        pstmt.executeUpdate();
        conn.close();
    }
    
    public static String getProjectID(String ProjectName) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spGetProjectID(?)}");
        cs.setString(1, ProjectName);
        rs = cs.executeQuery();
        String ProjectID = null;
        while(rs.next()){
            ProjectID = rs.getString(1);
        }
        return ProjectID;
    }
    
    public static String getProjectName(String ProjectID) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spGetProjectName(?)}");
        cs.setString(1, ProjectID);
        rs = cs.executeQuery();
        String ProjectName = null;
        while(rs.next()){
            ProjectName = rs.getString(1);
        }
        return ProjectName;
    }
    
    public static Project searchProjectByID(String ProjectID) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spSearchProjectByID(?)}");
        cs.setString(1, ProjectID);
        rs = cs.executeQuery();
        objProject = new Project();
        while (rs.next()) {            
            objProject.setStt(rs.getInt(1));
            objProject.setProjectID(rs.getString(2));
            objProject.setProjectName(rs.getString(3));
            objProject.setProjectAllowance(rs.getInt(4));
        }
        return objProject;
    }
    
    public static int searchProjectByName(String ProjectName) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spSearchProjectByName(?)}");
        cs.setString(1, ProjectName);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {
            count++;
        }
        return count;
    }
    
    public static int countSearchProjectByID(String projectID) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spSearchProjectByID(?)}");
        cs.setString(1, projectID);
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()){
            count++;
        }
        return count;
        
    }
    
    public static Vector getComboBoxProjectFromDB() throws SQLException {
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spProject}");
        rs = cs.executeQuery();
        project = new Vector();
        project.add("Select");
        while (rs.next()) {
            project.addElement(rs.getString(3));
        }
        return project;
    }
    
    
    
    public static Vector getComboBoxProjectIDSelect() throws SQLException {
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spProject}");
        rs = cs.executeQuery();
        project = new Vector();
        project.add("Select Project");
        while (rs.next()) {
            project.addElement(rs.getString(3));
        }
        return project;
    }
    
    public static int countTotalProject() throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spProject}");
        rs = cs.executeQuery();
        int count = 0;
        while (rs.next()) {            
            count++;
        }
        return count;
    }

    public static int getAllowance(String projectID) throws SQLException{
        conn = objDBUtil.connect();
        cs = conn.prepareCall("{call spGetAllowanceProject(?)}");
        cs.setString(1, projectID);
        rs = cs.executeQuery();
        int allowance = 0;
        while (rs.next()) {            
            allowance = rs.getInt(1);
        }
        return allowance;
    
    }
}
